﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace SqlHelper
{
    public class DBOperation
    {
        #region Propertys

        private SqlConnection mConnection = null;

        public SqlConnection Connection
        {
            get
            {
                return mConnection;
            }
            set
            {
                mConnection = value;
                if (mConnection.State == ConnectionState.Closed)
                {
                    mConnection.Open();
                }
            }
        }

        private Dictionary<Type, SqlDbType> TypeConvertDbTypeDic = new Dictionary<Type, SqlDbType>() {
            { typeof(string), SqlDbType.NVarChar },
            { typeof(int), SqlDbType.Int },
            { typeof(long), SqlDbType.BigInt},
            { typeof(decimal), SqlDbType.Decimal},
            { typeof(float), SqlDbType.Float},
            { typeof(bool), SqlDbType.Bit},
            { typeof(sbyte[]), SqlDbType.Binary},
            { typeof(byte[]), SqlDbType.Image},
            { typeof(DateTime), SqlDbType.DateTime},
        };
        
        #endregion

        #region Methods

        public DBOperation()
        { }

        public DBOperation(SqlConnection conn) : this()
        {
            Connection = conn;
        }

        public DBOperation(string ConnectionString) : this()
        {
            Connection = new SqlConnection(ConnectionString);
        }

        /// <summary>
        /// 更新指定数据集
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public DataSet UpdateDataSet(DataSet ds)
        {
            if (null == Connection) throw new NullReferenceException("the connection is not null");
            if (null == ds) throw new NullReferenceException("the update dataset is not null!");
            SqlTransaction tran = Connection.BeginTransaction();
            try
            {
                SqlBulkCopy bulkCopy = new SqlBulkCopy(Connection, SqlBulkCopyOptions.Default, tran);
                if (ds.Relations.Count > 0)
                {
                    foreach (DataRelation relation in ds.Relations)
                    {
                        CopyDataToServer(bulkCopy, relation.ParentTable);
                        CopyDataToServer(bulkCopy, relation.ChildTable);

                        Update(relation.ChildTable, tran);
                        Update(relation.ParentTable, tran);

                        relation.ParentTable.AcceptChanges();
                        relation.ChildTable.AcceptChanges();
                    }
                }
                foreach (DataTable table in ds.Tables)
                {
                    CopyDataToServer(bulkCopy, table);
                    Update(table, tran);
                    table.AcceptChanges();
                }
                tran.Commit();
                ds.AcceptChanges();
                bulkCopy.Close();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
            return ds;
        }

        /// <param name="table">准备更新的DataTable新数据</param>
        /// <param name="limitWhere">需要在ＳＱＬ的ＷＨＥＲＥ条件中限定的条件字符串，可为空。</param>
        /// <returns>返回更新的行数</returns>
        private int Update(DataTable table, SqlTransaction tran, string limitWhere = null)
        {
            DataSet ds = new DataSet();
            DataTable changeTable = table.GetChanges(DataRowState.Deleted | DataRowState.Detached | DataRowState.Modified);
            if (changeTable == null)
                return 0;
            ds.Tables.Add(changeTable);
            int result = 0;  
            try
            {
                string sqlstr = null;
                if (limitWhere != null && limitWhere.Length > 0)
                {
                    sqlstr = string.Format("select * from {0} where {1}", table.TableName, limitWhere);
                }
                else
                {
                    sqlstr = string.Format("select * from {0}", table.TableName);
                }
                //为Adapter定位目标表   
                SqlCommand cmd = new SqlCommand(sqlstr, Connection, tran);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da);
                da.AcceptChangesDuringUpdate = false;
                string columnsUpdateSql = "";
                SqlParameter[] paras = new SqlParameter[table.Columns.Count];
                SqlParameter[] up_pk_paras = new SqlParameter[table.PrimaryKey.Length];
                SqlParameter[] de_pk_paras = new SqlParameter[table.PrimaryKey.Length];
                //需要更新的列设置参数是,参数名为"@+列名"
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    DataColumn dc = table.Columns[i];
                    //此处拼接要更新的列名及其参数值
                    columnsUpdateSql += ("[" + dc.ColumnName + "]" + "=@" + dc.ColumnName + ",");
                    paras[i] = new SqlParameter("@" + dc.ColumnName, GetDbType(dc), dc.MaxLength, dc.ColumnName);
                    paras[i].SqlDbType = GetDbType(dc);
                }
                if (!string.IsNullOrEmpty(columnsUpdateSql))
                {
                    //此处去掉拼接处最后一个","
                    columnsUpdateSql = columnsUpdateSql.Remove(columnsUpdateSql.Length - 1);
                }
                //此处生成where条件语句
                string limitSql = "";
                for (int i = 0; i < table.PrimaryKey.Length; i++)
                {
                    DataColumn dc = table.PrimaryKey[i];
                    if (limitSql.Length > 0)
                    {
                        limitSql += " and ";
                    }
                    limitSql += ("[" + dc.ColumnName + "]" + "=@PK_" + dc.ColumnName);
                    up_pk_paras[i] = new SqlParameter("@PK_" + dc.ColumnName, GetDbType(dc), dc.MaxLength, dc.ColumnName);
                    de_pk_paras[i] = new SqlParameter("@PK_" + dc.ColumnName, GetDbType(dc), dc.MaxLength, dc.ColumnName);
                }
                SqlCommand updateCmd = new SqlCommand(string.Format("UPDATE [{0}] SET {1} WHERE {2} ", table.TableName, columnsUpdateSql, limitSql));
                SqlCommand deleteCmd = new SqlCommand(string.Format("DELETE FROM [{0}] WHERE {1}", table.TableName, limitSql));

                updateCmd.Transaction = tran;
                updateCmd.Connection = Connection;
                updateCmd.Parameters.AddRange(paras);
                updateCmd.Parameters.AddRange(up_pk_paras);

                deleteCmd.Transaction = tran;
                deleteCmd.Connection = Connection;
                deleteCmd.Parameters.AddRange(de_pk_paras);

                result = SqlHelper.UpdateDataset(null, deleteCmd, updateCmd, ds, table.TableName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 将数据批量插入到数据库中
        /// </summary>
        /// <param name="bulkCopy"></param>
        /// <param name="table"></param>
        private void CopyDataToServer(SqlBulkCopy bulkCopy, DataTable table)
        {
            try
            {
                bulkCopy.ColumnMappings.Clear();
                bulkCopy.DestinationTableName = table.TableName;
                foreach (DataColumn dc in table.Columns)
                {
                    bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                }
                bulkCopy.WriteToServer(table, DataRowState.Added);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private SqlDbType GetDbType(DataColumn dc)
        {
            SqlDbType dbType = default(SqlDbType);
            if (TypeConvertDbTypeDic.ContainsKey(dc.DataType))
            {
                dbType = TypeConvertDbTypeDic[dc.DataType];
            }
            return dbType;
        }

        public DataSet GetDataSet(string sqlCommand, params SqlParameter[] sqlParams)
        {
            if (null == Connection) throw new NullReferenceException("Connecton is not null");
            if (string.IsNullOrEmpty(sqlCommand)) throw new NullReferenceException("SqlCommand is not null");
            DataSet result = SqlHelper.ExecuteDataset(Connection, CommandType.Text, sqlCommand, sqlParams);
            return result;
        }

        /// <summary>
        /// 执行查询，并返回影响行数
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public int ExcuteNoQuery(string sqlCommand, params SqlParameter[] sqlParams)
        {
            if (null == Connection) throw new NullReferenceException("Connecton is not null");
            if (string.IsNullOrEmpty(sqlCommand)) throw new NullReferenceException("SqlCommand is not null");
            int result = SqlHelper.ExecuteNonQuery(Connection, CommandType.Text, sqlCommand, sqlParams);
            return result;
        }

        #endregion
    }
}
